﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace DbUpgradeOracle.General
{
    /// <summary>
    /// MySql帮助类
    /// </summary>
    public class OracleHelper
    {
        private string _connstring = string.Empty;
        //Data Source=filename;Version=3;Password=myPassword;
        private OracleConnection _conn = null;

        //事务
        OracleTransaction _tran = null;

        #region 实例
        /// <summary>
        /// 实例
        /// </summary>
        private static OracleHelper _instance;
        /// <summary>
        /// 实例
        /// </summary>
        public static OracleHelper Instance
        {
            get
            {
                if (_instance == null)
                {
                    _instance = new OracleHelper();
                }
                return _instance;
            }
        }
        #endregion

        private OracleHelper()
        {
            //ConfigManager config = new ConfigManager("config.json");
            //DbSet set = config.GetConfig<DbSet>("WebDbset");
            OracleConnectionStringBuilder conn_string = new OracleConnectionStringBuilder();
            conn_string.DataSource = App.ChooseDb.Ip + "/" + App.ChooseDb.DbName;
            conn_string.UserID = App.ChooseDb.UserName;
            conn_string.Password = App.ChooseDb.Password;
            _connstring = conn_string.ToString();
            //_connstring = $"server={set.Ip};database={set.Db};uid={set.User};pwd={set.Psd};";//ConfigurationManager.ConnectionStrings["conn"].ToString();
            _conn = new OracleConnection(_connstring);
            if (_conn.State == ConnectionState.Closed)
            {
                _conn.Open();
            }
        }

        private void OpenConn()
        {
            if (_conn.State == ConnectionState.Closed)
            {
                _conn.Open();
            }
        }

        public void BeginTran()
        {
            OpenConn();
            if (_tran != null)
            {
                _tran.Rollback();
                _tran = null;
            }
            _tran = _conn.BeginTransaction();
        }

        public void Commit()
        {
            OpenConn();

            if (_tran != null)
            {
                _tran.Commit();
                _tran = null;
                _conn.Close();
            }
        }

        public void Rollback()
        {
            OpenConn();

            if (_tran != null)
            {
                _tran.Rollback();
                _tran = null;
                _conn.Close();
            }
        }

        public DataSet ExecDataSet(string sql)
        {
            OpenConn();
            OracleCommand cmd = _conn.CreateCommand();
            cmd.CommandText = sql;
            DataSet ds = new DataSet();
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            da.Fill(ds);
            da.Dispose();
            //cmd.Connection.Close();
            cmd.Dispose();
            return ds;
        }

        public DataTable ExecDataTable(string sql)
        {
            OpenConn();
            OracleCommand cmd = _conn.CreateCommand();
            cmd.CommandText = sql;
            DataSet ds = new DataSet();
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            da.Fill(ds);
            da.Dispose();
            //cmd.Connection.Close();
            cmd.Dispose();
            DataTable dt = null;
            if (ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }
            return dt;
        }

        public int ExecNoQuery(string sql)
        {
            OpenConn();
            OracleCommand cmd = _conn.CreateCommand();
            cmd.CommandText = sql;
            int i = cmd.ExecuteNonQuery();
            //cmd.Connection.Close();
            cmd.Dispose();
            return i;
        }

        public void ExecNoQuerys(string sql)
        {
            List<string> sqls = FormatSql(sql);
            foreach (string item in sqls)
            {
                ExecNoQuery(item);
            }
            //StringBuilder sqlTemp = FormatSqlEx(sql);
            //string nsql = string.Format("BEGIN EXECUTE IMMEDIATE '{0}' END;", sqlTemp.ToString());
            //string nsql = string.Format("BEGIN {0} END;", sqlTemp.ToString());
            //ExecNoQuery(nsql);
        }

        public object ExecScalar(string sql)
        {
            OpenConn();
            OracleCommand cmd = _conn.CreateCommand();
            cmd.CommandText = sql;
            object o = cmd.ExecuteScalar();
            //cmd.Connection.Close();
            cmd.Dispose();
            return o;
        }

        private List<string> FormatSql(string sql)
        {
            string sqlTemp = ClearNote(sql);
            string[] sqlTemps = sqlTemp.Split(';');

            List<string> sqllist = new List<string>();
            foreach (string item in sqlTemps)
            {
                if (!string.IsNullOrEmpty(item.Trim()))
                {
                    sqllist.Add(item.Trim());
                }
            }
            return sqllist;
        }

        private StringBuilder FormatSqlEx(string sql)
        {
            string sqlTemp = ClearNote(sql);
            string[] sqlTemps = sqlTemp.Split(';');

            StringBuilder sb = new StringBuilder();
            foreach (string item in sqlTemps)
            {
                if (!string.IsNullOrEmpty(item.Trim()))
                {
                    string str = item.Trim();//.Replace("'", "''");
                    //str = string.Format(" EXECUTE IMMEDIATE '{0}'", str);
                    sb.Append(str + ";");
                }
            }
            return sb;
        }

        #region 清除注释
        /// <summary>
        /// 清除注释
        /// </summary>
        /// <param name="textSr"></param>
        /// <returns></returns>
        public string ClearNote(string sqlTemp)
        {
            sqlTemp = sqlTemp.Replace("@", "&%&%");
            sqlTemp = sqlTemp.Replace("\r\n", "\n");
            sqlTemp = sqlTemp.Replace("\n", "@");
            //去除"/****/"该类型注释
            Regex r = new Regex("\\/\\*.{0,}?\\*\\/");
            MatchCollection mc = r.Matches(sqlTemp);
            for (int i = 0; i < mc.Count; i++)
            {
                Match m = mc[i];
                sqlTemp = sqlTemp.Replace(m.Value, "");
            }
            //将剩余字符串按照换行转成数组
            string[] sqlTemps = sqlTemp.Split('@');
            string sql = string.Empty;
            foreach (string s in sqlTemps)
            {
                if (!string.IsNullOrEmpty(s.Trim()))
                {
                    string sClear = ClearOneLineNote(s);
                    if (!string.IsNullOrEmpty(sClear.Trim()))
                    {
                        sql += "@" + sClear;
                    }
                }
            }
            if (!string.IsNullOrEmpty(sql))
            {
                sql = sql.Remove(0, 1);
            }
            sql = sql.Replace("@", "\r\n");
            sql = sql.Replace("&%&%", "@");
            return sql;
        }
        #endregion

        #region 去除单行注释
        /// <summary>
        /// 去除单行注释
        /// </summary>
        /// <param name="line">单行原文</param>
        /// <returns>文本</returns>
        private string ClearOneLineNote(string line)
        {
            int i = line.IndexOf("--");
            string newLine = string.Empty;
            if (i >= 0)
            {
                newLine = line.Remove(i);
            }
            else
            {
                newLine = line;
            }
            return newLine;
        }
        #endregion
    }
}
